package uk.co.argos.ecommerce.dataload.extract;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Vector;

import uk.co.argos.dataload.impl.Loader;
import uk.co.argos.dataload.wc7.xml.util.DataLoadConstants;
import uk.co.argos.ecommerce.common.databeans.UserEventLogDataBean;
import uk.co.argos.ecommerce.common.databeans.UserEventLogDataBeanImpl;
import uk.co.argos.ecommerce.common.registry.RegistryEntries;
import uk.co.argos.ecommerce.common.utils.ArgosConstants;
import uk.co.homebase.ecommerce.common.HomebaseConstants;

public class DBAPODataExtract extends ExtractProcess {
	
    public static void main(String... p) {
    	runMe(p);
    }
    
    @Override
    protected void processStore(ExtractStore store) throws Exception {

    	if (store.property_option1.equals("summ")) {
    		runSummaryDataExtract(store);
    		updateOption(store, lastSummRun(), now.toString());
    	}
    	else {
    		log.warning("NO SUMMARY REPORT OPTION SPECIFIED - no further processing bypassed");
    	}


    	if (store.property_option2.equals("intel")) {
    		runSiteIntelligenceDataExtract(store);
    		updateOption(store, lastIntelRun(), now.toString());
    	}
    	else {
    		log.warning("NO INTELLIGENCE REPORT OPTION SPECIFIED - no further processing bypassed");
    	}

    }
    
    private String lastSummRun() {
    	return "SUMM_LASTRUN";
    }
    
    private String lastIntelRun() {
    	return "INTEL_LASTRUN";
    }
    
    private void runSummaryDataExtract(ExtractStore store) throws Exception {

        String delim = ",";
        String quote = "\"";
        
        FileWriter fileWriter = null;
        BufferedWriter bufferedWriter = null;
        String pathName;
    
        // Various date formats we need
        SimpleDateFormat simpleDateFormatDB     = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        SimpleDateFormat simpleDateFormatHdr    = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat simpleDateFormatFile   = new SimpleDateFormat("yyyyMMddHHmmss");
        
        // Get run times
        Timestamp startDateTime = Timestamp.valueOf(readOption(store, lastSummRun()));
        Timestamp endDateTime = now;

            int extract1CounterArgos    = getEventCountByTimeStampRange(startDateTime,
                                                endDateTime,
                                                ArgosConstants.STORE_ID,
                                                "DBAPO",
                                                "DBAPO-4");
            int extract1CounterHomebase    = getEventCountByTimeStampRange(startDateTime,
                                                endDateTime,
                                                HomebaseConstants.HOMEBASE_STORE_ID,
                                                "DBAPO",
                                                "DBAPO-4");
            int extract2CounterArgos    = getEventCountByTimeStampRange(startDateTime,
                                                endDateTime,
                                                ArgosConstants.STORE_ID,
                                                "DBAPO",
                                                "DBAPO-3");
            int extract2CounterHomebase    = getEventCountByTimeStampRange(startDateTime,
                                                endDateTime,
                                                HomebaseConstants.HOMEBASE_STORE_ID,
                                                "DBAPO",
                                                "DBAPO-3");
            int extract3CounterArgos    = getEventCountByTimeStampRange(startDateTime,
                                                endDateTime,
                                                ArgosConstants.STORE_ID,
                                                "DBAPO",
                                                "DBAPO-2");
            int extract3CounterHomebase    = getEventCountByTimeStampRange(startDateTime,
                                                endDateTime,
                                                HomebaseConstants.HOMEBASE_STORE_ID,
                                                "DBAPO",
                                                "DBAPO-2");


            fileWriter = new FileWriter( "DBAPO-sum-extract-" + simpleDateFormatFile.format(endDateTime) + ".csv");
            bufferedWriter = new BufferedWriter(fileWriter);
            bufferedWriter.write("Store ID: " + ArgosConstants.STORE_ID);
            bufferedWriter.newLine();
            bufferedWriter.write(simpleDateFormatHdr.format(startDateTime));
            bufferedWriter.write(" - ");
            bufferedWriter.write(simpleDateFormatHdr.format(endDateTime));
            bufferedWriter.newLine();
            bufferedWriter.write(quote + extract1CounterArgos + quote + delim);
            bufferedWriter.write(quote + extract2CounterArgos + quote + delim);
            bufferedWriter.write(quote + extract3CounterArgos + quote);
            bufferedWriter.newLine();
            bufferedWriter.flush();
            bufferedWriter.close();

            fileWriter.close();

            fileWriter = new FileWriter( "DBAPO-sum-extract-" + simpleDateFormatFile.format(endDateTime) + ".csv");
            bufferedWriter = new BufferedWriter(fileWriter);
            bufferedWriter.write("Store ID: " + HomebaseConstants.HOMEBASE_STORE_ID);
            bufferedWriter.newLine();
            bufferedWriter.write(simpleDateFormatHdr.format(startDateTime));
            bufferedWriter.write(" - ");
            bufferedWriter.write(simpleDateFormatHdr.format(endDateTime));
            bufferedWriter.newLine();
            bufferedWriter.write(quote + extract1CounterHomebase + quote + delim);
            bufferedWriter.write(quote + extract2CounterHomebase + quote + delim);
            bufferedWriter.write(quote + extract3CounterHomebase + quote);
            bufferedWriter.newLine();
            bufferedWriter.flush();
            bufferedWriter.close();
   
    }

    private void runSiteIntelligenceDataExtract(ExtractStore store) {

        String delim = ",";
        String quote = "\"";
    
        FileWriter fileWriter = null;
        BufferedWriter bufferedWriter = null;
    
        SimpleDateFormat simpleDateFormatDB     = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        SimpleDateFormat simpleDateFormatFile   = new SimpleDateFormat("yyyyMMddHHmmss");
        
        // Get run times
        Timestamp startDateTime  = new Timestamp(Timestamp.valueOf(getLastRunTime()).getTime());;
    
        Timestamp endDateTime = new Timestamp(new java.util.Date().getTime());

    
 
            Vector vector = findEventsByTimeStampRangeForComponent(startDateTime, endDateTime, "DBAPO");
            UserEventLogDataBean userEventLogDataBean = new UserEventLogDataBeanImpl();
            
            // Create a text file for output
            fileWriter = new FileWriter(  "DBAPO-Site-Intel-extract-" + simpleDateFormatFile.format(endDateTime) + ".csv");
            bufferedWriter = new BufferedWriter(fileWriter);
    
            String component    = new String();
            String timeStamp    = new String();
            String userId       = new String();
            String storeEntId   = new String();
            String orderId      = new String();
            String url          = new String();
            String statusCode   = new String();
            String reference    = new String();
    
            for (int i = 0; i < vector.size(); i++) {
                
                userEventLogDataBean = (UserEventLogDataBean)vector.elementAt(i);
                
                // Check status_code is eligible for report
                statusCode   = userEventLogDataBean.getStatusCode() == null ? "" : userEventLogDataBean.getStatusCode();
                if (isSIStatusCode(statusCode)) {
                    // Check for nulls
                    component    = userEventLogDataBean.getComponent()  == null ? "" : userEventLogDataBean.getComponent();
                    timeStamp    = userEventLogDataBean.getTimeStamp()  == null ? "" : userEventLogDataBean.getTimeStamp();
                    userId       = userEventLogDataBean.getUserId()     == null ? "" : userEventLogDataBean.getUserId().toString();
                    storeEntId   = userEventLogDataBean.getStoreEntId() == null ? "" : userEventLogDataBean.getStoreEntId().toString();
                    orderId      = userEventLogDataBean.getOrderId()    == null ? "" : userEventLogDataBean.getOrderId().toString();
                    url          = userEventLogDataBean.getUrl()        == null ? "" : userEventLogDataBean.getUrl();
                    // Already have status_code
                    reference    = userEventLogDataBean.getReference()  == null ? "" : userEventLogDataBean.getReference();
                    
                    // Write the data
                    bufferedWriter.write(quote + component + quote + delim);
                    bufferedWriter.write(quote + timeStamp + quote + delim);
                    bufferedWriter.write(quote + storeEntId + quote + delim);
                    bufferedWriter.write(quote + userId + quote + delim);
                    bufferedWriter.write(quote + orderId + quote + delim);
                    bufferedWriter.write(quote + url + quote + delim);
                    bufferedWriter.write(quote + statusCode + quote);
                    bufferedWriter.newLine();
                    bufferedWriter.flush();
                }    
            }
            
            bufferedWriter.close();
        
    }

    
    private Vector findEventsByTimeStampRangeForComponent(Timestamp startTimestamp, Timestamp endTimestamp, String component) throws Exception {

        Vector vector = new Vector();
        Date date;
        UserEventLogDataBean userEventLogDataBean;
        String componentSQL = new String();
        String startTimestampSQL = new String();
        String endTimestampSQL = new String();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
                
        //Check parameters
        if (startTimestamp != null) {
            startTimestampSQL = "timestamp >= '" + simpleDateFormat.format(startTimestamp) + "' ";
        }

        if (endTimestamp != null) {
            endTimestampSQL = "and timestamp < '" + simpleDateFormat.format(endTimestamp) + "' ";
        }

        if (component != null && !component.equals("")) {
            componentSQL = " component = '" + component + "' and ";  
        }

        StringBuffer theQuery = new StringBuffer(1024);


            //Obtain the results based on supplied input criteria.
            theQuery = new StringBuffer(1024);
            theQuery.append("select ");
            theQuery.append("component, ");
            theQuery.append("timestamp, ");
            theQuery.append("user_id, ");
            theQuery.append("storeent_id, ");
            theQuery.append("order_id, ");
            theQuery.append("url, ");
            theQuery.append("status_code, "); 
            theQuery.append("reference ");
            theQuery.append("from usereventlog ");
            theQuery.append("where ");
            theQuery.append(componentSQL);
            theQuery.append(startTimestampSQL);
            theQuery.append(endTimestampSQL);
            theQuery.append("order by ");
            theQuery.append("component, ");
            theQuery.append("status_code, ");
            theQuery.append("storeent_id, ");
            theQuery.append("user_id, ");
            theQuery.append("timestamp");
            
            PreparedStatement stmt = con.prepareStatement(theQuery.toString());
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                userEventLogDataBean = new UserEventLogDataBeanImpl();
                userEventLogDataBean.setComponent(rs.getString(1));
                userEventLogDataBean.setTimeStamp(simpleDateFormat.format(new Date(rs.getTimestamp(2).getTime())));
                userEventLogDataBean.setUserId(new Long(rs.getLong(3)));
                userEventLogDataBean.setStoreEntId(new Long(rs.getLong(4)));
                userEventLogDataBean.setOrderId(new Long(rs.getLong(5)));
                userEventLogDataBean.setUrl(rs.getString(6));
                userEventLogDataBean.setStatusCode(rs.getString(7));
                userEventLogDataBean.setReference(rs.getString(8));
                
                vector.addElement(userEventLogDataBean);
            }
            
            rs.close();
            stmt.close();
            
  
        return vector;

    }
    

    private int getEventCountByTimeStampRange(Timestamp startTimestamp, Timestamp endTimestamp, String storeId, String component, String statusCode) {

        Vector vector = new Vector();
        Date date;
        UserEventLogDataBean userEventLogDataBean;
        String componentSQL = new String();
        String startTimestampSQL = new String();
        String endTimestampSQL = new String();
        String storeIdSQL = new String();
        String statusCodeSQL = new String();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        int resultCount = 0;
        StringBuffer theQuery = new StringBuffer(1024);




            //Obtain the results based on supplied input criteria.
            theQuery = new StringBuffer(1024);
            theQuery.append("select ");
            theQuery.append("count (distinct o.orders_id) ");
            theQuery.append("from ");
            theQuery.append("usereventlog uel, ");
            theQuery.append("orders o, ");
            theQuery.append("ordistat ois ");
            theQuery.append("where ");
            theQuery.append(componentSQL);
            theQuery.append(startTimestampSQL);
            theQuery.append(endTimestampSQL);
            theQuery.append(storeIdSQL);
            theQuery.append(statusCodeSQL); 
            theQuery.append("o.orders_id = uel.order_id and ");
            theQuery.append("o.status = 'C' and ");
            theQuery.append("(o.sequence = 3 or "); 
            theQuery.append("o.sequence = 2) and ");
            theQuery.append("ois.orders_id = o.orders_id and ");
            theQuery.append("ois.oistatus = 'Success' and ");
            theQuery.append("ois.osmorder not like 'AD%' ");
            // Do not count orders where there are DBAPO-2 events
            // when there are also DBAPO-3 events (CR01) 
            if (statusCode.equals("DBAPO-2")) {
                theQuery.append("and ");
                theQuery.append("o.orders_id not in ( ");
                theQuery.append("select "); 
                theQuery.append("t1.order_id "); 
                theQuery.append("from "); 
                theQuery.append("usereventlog t1, "); 
                theQuery.append("usereventlog t2 "); 
                theQuery.append("where ");
                theQuery.append("t1.status_code = 'DBAPO-2' ");  
                theQuery.append("and "); 
                theQuery.append("t1.order_id=o.orders_id ");
                theQuery.append("and ");
                theQuery.append("t2.order_id=t1.order_id ");
                theQuery.append("and ");
                theQuery.append("t2.status_code = 'DBAPO-3' ) ");
            }
            // Do not count orders with DBAPO-4 events if there is a
            // more recent DBAPO-5 event (CR02)
            if (statusCode.equals("DBAPO-4")) {
                theQuery.append("and 'DBAPO-4' = ( ");
                theQuery.append("select ");
                theQuery.append("t1.status_code ");
                theQuery.append("from ");
                theQuery.append("usereventlog t1 ");
                theQuery.append("where ");
                theQuery.append("t1.order_id = o.orders_id and "); 
                theQuery.append("t1.timestamp = ( ");
                theQuery.append("select ");
                theQuery.append("max(timestamp) ");
                theQuery.append("from ");
                theQuery.append("usereventlog t2 ");
                theQuery.append("where ");
                theQuery.append("t2.status_code in ('DBAPO-4', 'DBAPO-5') and ");
                theQuery.append("t2.order_id = o.orders_id ");
                theQuery.append(")) ");
            }
            theQuery.append("for read only ");
            
            ResultSet rs = stmt.executeQuery();
    
            while (rs.next()) {
                resultCount = rs.getInt(1);
            }

        return resultCount;

    }


    
    /**
     * Check Site Intelligence are interested in this STATUS_CODE
     * @param status_code
     * @return boolean
     */
    private boolean isSIStatusCode(String status_code) {
        
        if (status_code == null || status_code.equals("")) return false;
        
            int i = new Integer(status_code.substring(status_code.indexOf('-') + 1)).intValue();
            
            switch (i) {
                case 4: return true;
                case 5: return true;
                case 6: return true;
                case 7: return true;
                case 8: return true;
                case 9: return true;
                default: return false;
            }
            
    }

	@Override
	protected String getRegistryTag() {
		
		return "EXTRACT_DBAPO_SUM";
		
	}

}